package com.javasm.finance.dao.impl;


import com.javasm.finance.dao.ProductCategoryDao;
import com.javasm.finance.entity.ProdChannel;
import com.javasm.finance.entity.ProductCategory;
import com.javasm.finance.entity.User;
import com.javasm.finance.util.DBUtils;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProductCategoryDaoImpl implements ProductCategoryDao {
    QueryRunner runner=new QueryRunner();

    @Override
    public List<ProductCategory> queryProductCategory(Integer page, Integer pageSize, String prodName) throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql = "select prod_id,prod_chineseName,prod_englishName,prod_channel_name " +
                "from fin_prod_series ";
        List paramList = new ArrayList();
        if (prodName != null && !"".equals(prodName)) {
            sql += "where prod_chineseName like ? ";
            paramList.add("%" + prodName + "%");
        }
        sql += "limit " + (page - 1) * pageSize + "," + pageSize;
        BeanListHandler<ProductCategory> beanListHandler = new BeanListHandler<>(ProductCategory.class, new BasicRowProcessor(new GenerousBeanProcessor()));
        List<ProductCategory> userList = runner.query(conn, sql, beanListHandler, paramList.toArray());
        DBUtils.getClose(conn, null, null);
        return userList;
    }

    @Override
    public long findTotalNum(String prodName) throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql = "select count(1) totalNum from fin_prod_series ";
        List paramList = new ArrayList();
        if (prodName != null && !"".equals(prodName)) {
            sql += "where prod_chineseName like ? ";
            paramList.add("%" + prodName + "%");
        }
        long totalNum = runner.query(conn, sql, new ScalarHandler<>(), paramList.toArray());
        DBUtils.getClose(conn, null, null);
        return totalNum;
    }

    @Override
    public int addProductCategory(ProductCategory productCategory) throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql = "insert into fin_prod_series(prod_chineseName,prod_englishName,prod_parent_channel,prod_firsClass,prod_channel_name) values (?,?,?,?,?)";
        int rows = runner.update(conn, sql,productCategory.getProdChineseName(),
                productCategory.getProdEnglishName(),
                productCategory.getProdParentChannel(),
                productCategory.getProdFirsClass(),
                productCategory.getProdChannelName());
        DBUtils.getClose(conn, null, null);
        return rows;
    }

    @Override
    public ProductCategory findByPid(Integer prodId) throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql = "select prod_id,prod_chineseName,prod_englishName,prod_parent_channel,prod_firsClass " +
                "from fin_prod_series " +
                "where prod_id=?";
        BeanHandler<ProductCategory> beanHandler = new BeanHandler<>(ProductCategory.class, new BasicRowProcessor(new GenerousBeanProcessor()));
        ProductCategory category = runner.query(conn, sql, beanHandler, prodId);
        DBUtils.getClose(conn, null, null);
        return category;
    }

    @Override
    public int editProd(ProductCategory productCategory) throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql = "update " +
                "fin_prod_series " +
                "set " +
                "prod_chineseName=?,prod_englishName=?,prod_parent_channel=?,prod_firsClass=? " +
                "where " +
                "prod_id=?";
        int rows = runner.update(conn, sql, productCategory.getProdChineseName(),
                productCategory.getProdEnglishName(),
                productCategory.getProdParentChannel(),
                productCategory.getProdFirsClass(),
                productCategory.getProdId());
        DBUtils.getClose(conn, null, null);
        return rows;
    }

    @Override
    public List<ProdChannel> queryChannel() throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql = "select prod_channel_id,prod_channel_name from pro_channel;";
        BeanListHandler<ProdChannel> beanListHandler = new BeanListHandler<>(ProdChannel.class, new BasicRowProcessor(new GenerousBeanProcessor()));
        List<ProdChannel> list = runner.query(conn, sql, beanListHandler);
        DBUtils.getClose(conn, null, null);
        return list;
    }

    @Override
    public ProductCategory findByPidRemi(Integer prodId) throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql = "select prod_id,remi_bankName,remi_swift,remi_bankCode,remi_cnaps,remi_bankAddress,remi_peopleName,remi_account,remi_peopleAddress,prod_channel_name " +
                "from fin_prod_series " +
                "where prod_id=?";
        BeanHandler<ProductCategory> beanHandler = new BeanHandler<>(ProductCategory.class, new BasicRowProcessor(new GenerousBeanProcessor()));
        ProductCategory category = runner.query(conn, sql, beanHandler, prodId);
        DBUtils.getClose(conn, null, null);
        return category;
    }

    @Override
    public int addRemi(ProductCategory productCategory) throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql = "update " +
                "fin_prod_series " +
                "set " +
                "remi_bankName=?,remi_swift=?,remi_bankCode=?,remi_cnaps=?,remi_bankAddress=?,remi_peopleName=?,remi_account=?,remi_peopleAddress=?,prod_channel_name=? " +
                "where " +
                "prod_id=?";
        int rows = runner.update(conn, sql,productCategory.getRemiBankName(),
                productCategory.getRemiSwift(),
                productCategory.getRemiBankCode(),
                productCategory.getRemiCnaps(),
                productCategory.getRemiBankAddress(),
                productCategory.getRemiPeopleName(),
                productCategory.getRemiAccount(),
                productCategory.getRemiPeopleAddress(),
                productCategory.getProdChannelName(),
                productCategory.getProdId());
        DBUtils.getClose(conn, null, null);
        return rows;
    }

    @Override
    public int deleteProd(Integer prodId) throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql = "delete from fin_prod_series where prod_id=?";
        int rows = runner.update(conn, sql, prodId);
        DBUtils.getClose(conn, null, null);
        return rows;
    }


}
